﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using Org.SqlHelper;
using log4net;

namespace QualityRpt.Dao.Db2
{
    class AIRWIRETransByDayDao:IAIRWIRETransByDayDao
    {
        static ILog logger = LogManager.GetLogger("AIRWIRETransByDayDao");
        const string cachePrefix = "TRNT-";
        public DataTable GetAIRStatByMQ(DateTime startTime, DateTime endTime, int statTType, string spin, string specin,
           string rdin, string plin, string lnin)
        {
            DateTime trackStart = DateTime.Now;
            StringBuilder sbS = new StringBuilder();
            StringBuilder sbW = new StringBuilder();
            StringBuilder sbG = new StringBuilder();
            DataTable rtTable = null;
            #region 条件处理
            sbW.Append("TR.SUM_DATE>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.SUM_DATE<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.DATASOURCE=1 ");
            bool hasGroup = false;
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.AIRWIRE_SUPPLIER_ID IN (" + spin + ")");
                sbS.Append("TR.AIRWIRE_SUPPLIER_NAME AS SUPPLIER_NAME,");
                sbG.Append("TR.AIRWIRE_SUPPLIER_NAME,");
                hasGroup = true;
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.AIRWIRE_SPEC_ID IN (" + specin + ")");
                sbS.Append("TR.AIRWIRE_SPEC_NAME AS SPEC_NAME,");
                sbG.Append("TR.AIRWIRE_SPEC_NAME,");
                hasGroup = true;
            }
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.ROAD_ID IN (" + rdin + ")");
                sbS.Append("TR.ROAD_NAME,");
                sbG.Append("TR.ROAD_NAME,");
                hasGroup = true;
            }
            if (!hasGroup)
            {
                sbS.Append("TR.ROAD_NAME,TR.AIRWIRE_SUPPLIER_NAME AS SUPPLIER_NAME,TR.AIRWIRE_SPEC_NAME AS SPEC_NAME,");
                sbG.Append("TR.ROAD_NAME,TR.AIRWIRE_SUPPLIER_NAME,TR.AIRWIRE_SPEC_NAME,");
            }
            //where
            if (plin != null && plin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.PLAZA_ID IN (" + plin + ")");
            }
            if (lnin != null && lnin.Trim() != string.Empty)
            {
                sbW.Append(" AND TR.LANE_NO IN (" + lnin + ")");
            }
            //时间分组情况
            if (statTType > 0)
            {
                switch (statTType)
                {
                    case 1://按月
                        sbS.Append("YEAR(TR.SUM_DATE) AS STYEAR,MONTH(TR.SUM_DATE) AS STMONTH,");
                        sbG.Append("YEAR(TR.SUM_DATE),MONTH(TR.SUM_DATE),");
                        break;
                    case 2://按周
                        sbS.Append("YEAR(TR.SUM_DATE) AS STYEAR,WEEK_ISO(TR.SUM_DATE) AS STWEEK,");
                        sbG.Append("YEAR(TR.SUM_DATE),WEEK_ISO(TR.SUM_DATE),");
                        break;
                    case 3://按日
                        sbS.Append("YEAR(TR.SUM_DATE) AS STYEAR,MONTH(TR.SUM_DATE) AS STMONTH,DAY(TR.SUM_DATE) AS STDAY,");
                        sbG.Append("YEAR(TR.SUM_DATE),MONTH(TR.SUM_DATE),DAY(TR.SUM_DATE),");
                        break;
                }
            }
            #endregion
            sbS.Append("TR.ICARDWORK_MODE AS ICARD_WORKMODE"); sbG.Append("TR.ICARDWORK_MODE");
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("SELECT " + sbS.ToString() + ",SUM(TRANS_COUNT) AS TRANSCOUNT FROM RPT.AIRWIRE_TRANS_BY_DAY AS TR WHERE " + sbW.ToString() + " GROUP BY " + sbG.ToString());
            
            string cacheKey = cachePrefix + sbSql.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbSql.ToString());
                }
                CacheManager.Put(cacheKey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            TimeSpan tsConsume = DateTime.Now - trackStart;
            logger.Info(sbSql.ToString() + "耗时:" + tsConsume.TotalSeconds + "s");
            return rtTable;
        }
    }
}
